You can set a column as a prefix or suffix for a given measure from the Measure Settings. This means that when the measure is added to reports, the units or currency will be displayed dynamically in each data cell.
This differs from static formats, which are set by applying one format mask to the measure. Static formats are useful if all the measure's values have the same unit or currency. If, however, the measure contains values with different units or currencies (like euros and dollars, for example), dynamic formatting should be used.
Note: this feature is not available in the Community Edition.
How to Set Dynamic Formatting
Dynamic formatting is set from the Measure Editor; set the prefix or suffix by choosing the required column from the relevant drop-down (green highlight below). The Prefix and Suffix columns will display the columns that are located in the same table as the given measure. This means that if your formatting column is stored in a different table, you must add a join to the relevant tables in the Data Flow.
- Click here to learn about joins.
Format Prefix Column
Choose the column that lists the prefixes for the given measure.
Example. Prefix
In this example, we have a table that fact table that contains sales and currency. The currency is a mix of AUD and USD.
From the Tables panel, the Sales measure is edited by setting Currency as the Format Prefix Column:
When the Sales measure is added to a query, its format is driven by the Currency column:
Format Suffix Column
Choose the column that lists the suffixes for the given measure.
Example. Suffix
In this example, the Amount measure is located in the Fact table, while the Currency column is stored in the Currency table. As a prefix or suffix must be located in the same table as the measure itself, a join was added to the relevant tables in the Data Flow.
From the Tables panel, the Amount measure was edited to set Currency as the Format Suffix Column:
When the Amount measure is added to the query, and broken down by a hierarchy (in this case, the date column), we see the currency is displayed dynamically. Note the cells where the suffix appears as an asterisk (green arrows below); this means that the value represents both EUR and USD.
